/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.apt.countrytag;

import com.apt.tag.Connect_DB;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Bach
 */
public class NewCoutryName {

    public static boolean setNewCountry(String country_Name, String city_Name) {
        String up = "INSERT INTO [Matrimony].[dbo].[Countries]\n"
                + "           ([Country_Status]\n"
                + "           ,[Country_Name])\n"
                + "     VALUES (1,?)";

        boolean re = false;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = Connect_DB.getConnection();
            con.setAutoCommit(false);
            int country_Id = 0;
            if (checkusername(country_Name, con)) {
                ps = con.prepareStatement(up, ps.RETURN_GENERATED_KEYS);
                ps.setString(1, country_Name);
                int i = ps.executeUpdate();
                rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    country_Id = rs.getInt(1);
                    if (setNewCity(city_Name, country_Id, con)) {
                        con.commit();
                        re = true;
                    }
                }
            } else {
                System.out.println("INSERT INTO [Matrimony].[dbo].[Countries] Fail");
            }

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(NewCoutryName.class.getName()).log(Level.SEVERE, null, ex);
            }
        } finally {
            Connect_DB.close(null, null, ps, con);
        }
        return re;
    }

    public static boolean checkusername(String name, Connection cnn) {
        boolean re = true;
        PreparedStatement ps = null;
        String qurey = "SELECT [Country_Id]\n"
                + "      ,[Country_Status]\n"
                + "      ,[Country_Name]\n"
                + "  FROM [Matrimony].[dbo].[Countries]"
                + " Where [Country_Name]=? ";
        ResultSet rs = null;
        try {
            ps = cnn.prepareStatement(qurey);
            ps.setString(1, name);
            rs = ps.executeQuery();
            if (rs.next()) {
                re = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return re;
    }

    public static boolean setNewCity(String city_Name, int country_Id, Connection cnn) {
        String up = "INSERT INTO [Matrimony].[dbo].[Cities]\n"
                + "           ([Country_Id]\n"
                + "           ,[City_Name]\n"
                + "           ,[City_Status])\n"
                + "     VALUES (?,?,1)";
        boolean re = false;
        PreparedStatement ps = null;
        try {
            ps = cnn.prepareStatement(up);
            ps.setInt(1, country_Id);
            ps.setString(2, city_Name);
            int i = ps.executeUpdate();
            if (i > 0) {
                re = true;
            } else {
                System.out.println("INSERT INTO [Matrimony].[dbo].[Cities] Fail");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return re;
    }
}
